﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Aliyeye.Car.Net.Utility;

namespace Aliyeye.Car.Net.Dal
{
    public class Reserve
    {
        public Reserve() { }
        public int Add(Model.Reserve model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4),
					new SqlParameter("@Code", SqlDbType.NVarChar,50),
					new SqlParameter("@CustId", SqlDbType.Int,4),
					new SqlParameter("@CarId", SqlDbType.Int,4),
					new SqlParameter("@Rent", SqlDbType.Decimal,9),
					new SqlParameter("@Deposit", SqlDbType.Decimal,9),
					new SqlParameter("@ZPrice", SqlDbType.Decimal,9),
					new SqlParameter("@DeadLine", SqlDbType.Int,4),
					new SqlParameter("@UserId", SqlDbType.Int,4),
					new SqlParameter("@BookTime", SqlDbType.DateTime),
					new SqlParameter("@Marks", SqlDbType.NVarChar,500),
					new SqlParameter("@InpuTime", SqlDbType.DateTime),
					new SqlParameter("@BPrice", SqlDbType.Decimal,9),
					new SqlParameter("@IsStar", SqlDbType.Bit,1),
					new SqlParameter("@ysTime", SqlDbType.NVarChar,50),
					new SqlParameter("@LivAddr", SqlDbType.NVarChar,150),
					new SqlParameter("@AriAddr", SqlDbType.NVarChar,150),
					new SqlParameter("@FlayCode", SqlDbType.NVarChar,50),
					new SqlParameter("@SenAddr", SqlDbType.NVarChar,150),
					new SqlParameter("@PayType", SqlDbType.NVarChar,50),
					new SqlParameter("@Type", SqlDbType.Int,4),
					new SqlParameter("@EndBooktime", SqlDbType.DateTime),
					new SqlParameter("@Endystime", SqlDbType.NVarChar,50),
					new SqlParameter("@OrderSataus", SqlDbType.Bit,1),
					new SqlParameter("@PriceType1", SqlDbType.NVarChar,150),
					new SqlParameter("@PriceType2", SqlDbType.NVarChar,150),
					new SqlParameter("@OutdatedFee", SqlDbType.Decimal),
					new SqlParameter("@DeadLineType", SqlDbType.NVarChar,150),
					new SqlParameter("@IllegalRetention", SqlDbType.Decimal,9),
					new SqlParameter("@CurrentMax", SqlDbType.BigInt),
					new SqlParameter("@IsGetCar", SqlDbType.Bit,1),
					new SqlParameter("@Points", SqlDbType.BigInt)
                                        };
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = model.Code;
            parameters[2].Value = model.CustId;
            parameters[3].Value = model.CarId;
            parameters[4].Value = model.Rent;
            parameters[5].Value = model.Deposit;
            parameters[6].Value = model.ZPrice;
            parameters[7].Value = model.DeadLine;
            parameters[8].Value = model.UserId;
            parameters[9].Value = model.BookTime;
            parameters[10].Value = model.Marks;
            parameters[11].Value = model.InpuTime;
            parameters[12].Value = model.BPrice;
            parameters[13].Value = model.IsStar;
            parameters[14].Value = model.ysTime;
            parameters[15].Value = model.LivAddr;
            parameters[16].Value = model.AriAddr;
            parameters[17].Value = model.FlayCode;
            parameters[18].Value = model.SenAddr;
            parameters[19].Value = model.PayType;
            parameters[20].Value = model.Type;
            parameters[21].Value = model.EndBooktime;
            parameters[22].Value = model.Endystime;
            parameters[23].Value = model.OrderSataus;
            parameters[24].Value = model.PriceType1;
            parameters[25].Value = model.PriceType2;
            parameters[26].Value = model.OutdatedFee;
            parameters[27].Value = model.DeadLineType;
            parameters[28].Value = model.IllegalRetention;
            parameters[29].Value = model.CurrentMax;
            parameters[30].Value = model.IsGetCar;
            parameters[31].Value = model.Points;

            try
            {
                int roleId = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "Reserve_ADD", parameters);
                if (roleId > 0)
                {
                    return Convert.ToInt32(parameters[0].Value);
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
        public bool Update(Model.Reserve model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4),
					new SqlParameter("@Code", SqlDbType.NVarChar,50),
					new SqlParameter("@CustId", SqlDbType.Int,4),
					new SqlParameter("@CarId", SqlDbType.Int,4),
					new SqlParameter("@Rent", SqlDbType.Decimal,9),
					new SqlParameter("@Deposit", SqlDbType.Decimal,9),
					new SqlParameter("@ZPrice", SqlDbType.Decimal,9),
					new SqlParameter("@DeadLine", SqlDbType.Int,4),
					new SqlParameter("@UserId", SqlDbType.Int,4),
					new SqlParameter("@BookTime", SqlDbType.DateTime),
					new SqlParameter("@Marks", SqlDbType.NVarChar,500),
					new SqlParameter("@InpuTime", SqlDbType.DateTime),
					new SqlParameter("@BPrice", SqlDbType.Decimal,9),
					new SqlParameter("@IsStar", SqlDbType.Bit,1),
					new SqlParameter("@ysTime", SqlDbType.NVarChar,50),
					new SqlParameter("@LivAddr", SqlDbType.NVarChar,150),
					new SqlParameter("@AriAddr", SqlDbType.NVarChar,150),
					new SqlParameter("@FlayCode", SqlDbType.NVarChar,50),
					new SqlParameter("@SenAddr", SqlDbType.NVarChar,150),
					new SqlParameter("@PayType", SqlDbType.NVarChar,50),
					new SqlParameter("@Type", SqlDbType.Int,4),
					new SqlParameter("@EndBooktime", SqlDbType.DateTime),
					new SqlParameter("@Endystime", SqlDbType.NVarChar,50),
					new SqlParameter("@OrderSataus", SqlDbType.Bit,1),
					new SqlParameter("@PriceType1", SqlDbType.NVarChar,150),
					new SqlParameter("@PriceType2", SqlDbType.NVarChar,150),
					new SqlParameter("@OutdatedFee", SqlDbType.Decimal),
					new SqlParameter("@DeadLineType", SqlDbType.NVarChar,150),
					new SqlParameter("@CurrentMax", SqlDbType.BigInt),
					new SqlParameter("@IsGetCar", SqlDbType.Bit,1)
                                        };
            parameters[0].Value = model.Id;
            parameters[1].Value = model.Code;
            parameters[2].Value = model.CustId;
            parameters[3].Value = model.CarId;
            parameters[4].Value = model.Rent;
            parameters[5].Value = model.Deposit;
            parameters[6].Value = model.ZPrice;
            parameters[7].Value = model.DeadLine;
            parameters[8].Value = model.UserId;
            parameters[9].Value = model.BookTime;
            parameters[10].Value = model.Marks;
            parameters[11].Value = model.InpuTime;
            parameters[12].Value = model.BPrice;
            parameters[13].Value = model.IsStar;
            parameters[14].Value = model.ysTime;
            parameters[15].Value = model.LivAddr;
            parameters[16].Value = model.AriAddr;
            parameters[17].Value = model.FlayCode;
            parameters[18].Value = model.SenAddr;
            parameters[19].Value = model.PayType;
            parameters[20].Value = model.Type;
            parameters[21].Value = model.EndBooktime;
            parameters[22].Value = model.Endystime;
            parameters[23].Value = model.OrderSataus;
            parameters[24].Value = model.PriceType1;
            parameters[25].Value = model.PriceType2;
            parameters[26].Value = model.OutdatedFee;
            parameters[27].Value = model.DeadLineType;
            parameters[28].Value = model.CurrentMax;
            parameters[29].Value = model.IsGetCar;

            int intResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "Reserve_Update", parameters);

            if (intResult > 0)
                return true;
            else
                return false;
        }

        public bool GetCar(int reserveID, int carID, Int64 currentMax,decimal decimalRent,string strPayType, decimal decimalZPrice)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@ReserveID", SqlDbType.Int,4),
					new SqlParameter("@CarId", SqlDbType.Int,4),
					new SqlParameter("@CurrentMax", SqlDbType.BigInt),
					new SqlParameter("@Rent", SqlDbType.Decimal,9),
					new SqlParameter("@PayType", SqlDbType.NVarChar,50),
					new SqlParameter("@ZPrice", SqlDbType.Decimal,9)
                                        };
            parameters[0].Value = reserveID;
            parameters[1].Value = carID;
            parameters[2].Value = currentMax;
            parameters[3].Value = decimalRent;
            parameters[4].Value = strPayType;
            parameters[5].Value = decimalZPrice;

            int intResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "Reserve_GetCar", parameters);

            if (intResult > 0)
                return true;
            else
                return false;
        }

        public bool Delete(int Id)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = Id;
            int intResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "Reserve_Delete", parameters);

            if (intResult > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.Reserve GetModel(int Id)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = Id;

            Model.Reserve model = new Model.Reserve();
            DataSet ds = SqlHelper.GetDataFormDataSet(CommandType.StoredProcedure, "Reserve_GetModel", parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                model.Code = ds.Tables[0].Rows[0]["Code"].ToString();
                if (ds.Tables[0].Rows[0]["CustId"].ToString() != "")
                {
                    model.CustId = int.Parse(ds.Tables[0].Rows[0]["CustId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CarId"].ToString() != "")
                {
                    model.CarId = int.Parse(ds.Tables[0].Rows[0]["CarId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Rent"].ToString() != "")
                {
                    model.Rent = decimal.Parse(ds.Tables[0].Rows[0]["Rent"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Deposit"].ToString() != "")
                {
                    model.Deposit = decimal.Parse(ds.Tables[0].Rows[0]["Deposit"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ZPrice"].ToString() != "")
                {
                    model.ZPrice = decimal.Parse(ds.Tables[0].Rows[0]["ZPrice"].ToString());
                }
                if (ds.Tables[0].Rows[0]["DeadLine"].ToString() != "")
                {
                    model.DeadLine = int.Parse(ds.Tables[0].Rows[0]["DeadLine"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UserId"].ToString() != "")
                {
                    model.UserId = int.Parse(ds.Tables[0].Rows[0]["UserId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["BookTime"].ToString() != "")
                {
                    model.BookTime = DateTime.Parse(ds.Tables[0].Rows[0]["BookTime"].ToString());
                }
                model.Marks = ds.Tables[0].Rows[0]["Marks"].ToString();
                if (ds.Tables[0].Rows[0]["InpuTime"].ToString() != "")
                {
                    model.InpuTime = DateTime.Parse(ds.Tables[0].Rows[0]["InpuTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["BPrice"].ToString() != "")
                {
                    model.BPrice = decimal.Parse(ds.Tables[0].Rows[0]["BPrice"].ToString());
                }
                if (ds.Tables[0].Rows[0]["IsStar"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["IsStar"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsStar"].ToString().ToLower() == "true"))
                    {
                        model.IsStar = true;
                    }
                    else
                    {
                        model.IsStar = false;
                    }
                }
                model.ysTime = ds.Tables[0].Rows[0]["ysTime"].ToString();
                model.LivAddr = ds.Tables[0].Rows[0]["LivAddr"].ToString();
                model.AriAddr = ds.Tables[0].Rows[0]["AriAddr"].ToString();
                model.FlayCode = ds.Tables[0].Rows[0]["FlayCode"].ToString();
                model.SenAddr = ds.Tables[0].Rows[0]["SenAddr"].ToString();
                model.PayType = ds.Tables[0].Rows[0]["PayType"].ToString();
                if (ds.Tables[0].Rows[0]["Type"].ToString() != "")
                {
                    model.Type = int.Parse(ds.Tables[0].Rows[0]["Type"].ToString());
                }
                if (ds.Tables[0].Rows[0]["EndBooktime"].ToString() != "")
                {
                    model.EndBooktime = DateTime.Parse(ds.Tables[0].Rows[0]["EndBooktime"].ToString());
                }
                model.Endystime = ds.Tables[0].Rows[0]["Endystime"].ToString();
                model.CustomerCode = ds.Tables[0].Rows[0]["CustomerCode"].ToString();
                model.CustomerName = ds.Tables[0].Rows[0]["Name"].ToString();
                model.UserTrueName = ds.Tables[0].Rows[0]["UserTrueName"].ToString();
                model.Tel = ds.Tables[0].Rows[0]["Tel"].ToString();
                model.ComName = ds.Tables[0].Rows[0]["ComName"].ToString();
                model.CustomerAddr = ds.Tables[0].Rows[0]["CustomerAddr"].ToString();
                model.CustomerDriCard = ds.Tables[0].Rows[0]["CustomerDriCard"].ToString();
                model.CustomerCodeCar = ds.Tables[0].Rows[0]["CustomerCodeCar"].ToString();
                model.CustomerPhone = ds.Tables[0].Rows[0]["CustomerPhone"].ToString();
                model.CustomerType = ds.Tables[0].Rows[0]["CustomerType"].ToString();

                model.PriceType1 = ds.Tables[0].Rows[0]["PriceType1"].ToString();
                model.PriceType2 = ds.Tables[0].Rows[0]["PriceType2"].ToString();
                if (ds.Tables[0].Rows[0]["OutdatedFee"].ToString() != "")
                {
                    model.OutdatedFee = decimal.Parse(ds.Tables[0].Rows[0]["OutdatedFee"].ToString());
                }
                model.DeadLineType = ds.Tables[0].Rows[0]["DeadLineType"].ToString();
                if (ds.Tables[0].Rows[0]["IllegalRetention"].ToString() != "")
                {
                    model.IllegalRetention = decimal.Parse(ds.Tables[0].Rows[0]["IllegalRetention"].ToString());
                }

                if (ds.Tables[0].Rows[0]["RentReceivable"].ToString() != "")
                {
                    model.RentReceivable = decimal.Parse(ds.Tables[0].Rows[0]["RentReceivable"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ReceivedAmount"].ToString() != "")
                {
                    model.ReceivedAmount = decimal.Parse(ds.Tables[0].Rows[0]["ReceivedAmount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["OvertimeAmount"].ToString() != "")
                {
                    model.OvertimeAmount = decimal.Parse(ds.Tables[0].Rows[0]["OvertimeAmount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SuperDriverAmount"].ToString() != "")
                {
                    model.SuperDriverAmount = decimal.Parse(ds.Tables[0].Rows[0]["SuperDriverAmount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["TotalFines"].ToString() != "")
                {
                    model.TotalFines = decimal.Parse(ds.Tables[0].Rows[0]["TotalFines"].ToString());
                }
                if (ds.Tables[0].Rows[0]["RepairDamage"].ToString() != "")
                {
                    model.RepairDamage = decimal.Parse(ds.Tables[0].Rows[0]["RepairDamage"].ToString());
                }
                if (ds.Tables[0].Rows[0]["TotalAmount"].ToString() != "")
                {
                    model.TotalAmount = decimal.Parse(ds.Tables[0].Rows[0]["TotalAmount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SettlementDate"].ToString() != "")
                {
                    model.SettlementDate = Convert.ToDateTime(ds.Tables[0].Rows[0]["SettlementDate"].ToString());
                }

                if (ds.Tables[0].Rows[0]["CurrentMax"].ToString() != "")
                {
                    model.CurrentMax = Convert.ToInt64(ds.Tables[0].Rows[0]["CurrentMax"].ToString());
                }
                else
                    model.CurrentMax = 0;

                if (ds.Tables[0].Rows[0]["IsGetCar"].ToString() != "")
                {
                    model.IsGetCar = Convert.ToBoolean(ds.Tables[0].Rows[0]["IsGetCar"].ToString());
                }
                else
                    model.IsGetCar = false;

                return model;
            }
            else
            {
                return null;
            }
        }

        public Model.Reserve GetModel(string Code)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("sselect Id,Code,CustId,CarId,Rent,Deposit,ZPrice,DeadLine,UserId,BookTime,Marks,InpuTime,BPrice,IsStar,ysTime,LivAddr,AriAddr,FlayCode,SenAddr,PayType,Type,EndBooktime,Endystime,PriceType1,PriceType2,OutdatedFee,DeadLineType,CurrentMax,IsGetCar,EarlyRefund  from Reserve ");
            strSql.Append(" where Code=@Code ");
            SqlParameter[] parameters = {
					new SqlParameter("@Code", SqlDbType.NVarChar,50)};
            parameters[0].Value = Code;

            Model.Reserve model = new Model.Reserve();
            DataSet ds = SqlHelper.GetDataFormDataSet(CommandType.Text, strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                model.Code = ds.Tables[0].Rows[0]["Code"].ToString();
                if (ds.Tables[0].Rows[0]["CustId"].ToString() != "")
                {
                    model.CustId = int.Parse(ds.Tables[0].Rows[0]["CustId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CarId"].ToString() != "")
                {
                    model.CarId = int.Parse(ds.Tables[0].Rows[0]["CarId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Rent"].ToString() != "")
                {
                    model.Rent = decimal.Parse(ds.Tables[0].Rows[0]["Rent"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Deposit"].ToString() != "")
                {
                    model.Deposit = decimal.Parse(ds.Tables[0].Rows[0]["Deposit"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ZPrice"].ToString() != "")
                {
                    model.ZPrice = decimal.Parse(ds.Tables[0].Rows[0]["ZPrice"].ToString());
                }
                if (ds.Tables[0].Rows[0]["DeadLine"].ToString() != "")
                {
                    model.DeadLine = int.Parse(ds.Tables[0].Rows[0]["DeadLine"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UserId"].ToString() != "")
                {
                    model.UserId = int.Parse(ds.Tables[0].Rows[0]["UserId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["BookTime"].ToString() != "")
                {
                    model.BookTime = DateTime.Parse(ds.Tables[0].Rows[0]["BookTime"].ToString());
                }
                model.Marks = ds.Tables[0].Rows[0]["Marks"].ToString();
                if (ds.Tables[0].Rows[0]["InpuTime"].ToString() != "")
                {
                    model.InpuTime = DateTime.Parse(ds.Tables[0].Rows[0]["InpuTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["BPrice"].ToString() != "")
                {
                    model.BPrice = decimal.Parse(ds.Tables[0].Rows[0]["BPrice"].ToString());
                }
                if (ds.Tables[0].Rows[0]["IsStar"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["IsStar"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsStar"].ToString().ToLower() == "true"))
                    {
                        model.IsStar = true;
                    }
                    else
                    {
                        model.IsStar = false;
                    }
                }
                model.ysTime = ds.Tables[0].Rows[0]["ysTime"].ToString();
                model.LivAddr = ds.Tables[0].Rows[0]["LivAddr"].ToString();
                model.AriAddr = ds.Tables[0].Rows[0]["AriAddr"].ToString();
                model.FlayCode = ds.Tables[0].Rows[0]["FlayCode"].ToString();
                model.SenAddr = ds.Tables[0].Rows[0]["SenAddr"].ToString();
                model.PayType = ds.Tables[0].Rows[0]["PayType"].ToString();
                if (ds.Tables[0].Rows[0]["Type"].ToString() != "")
                {
                    model.Type = int.Parse(ds.Tables[0].Rows[0]["Type"].ToString());
                }
                if (ds.Tables[0].Rows[0]["EndBooktime"].ToString() != "")
                {
                    model.EndBooktime = DateTime.Parse(ds.Tables[0].Rows[0]["EndBooktime"].ToString());
                }
                model.Endystime = ds.Tables[0].Rows[0]["Endystime"].ToString();

                model.PriceType1 = ds.Tables[0].Rows[0]["PriceType1"].ToString();
                model.PriceType2 = ds.Tables[0].Rows[0]["PriceType2"].ToString();
                if (ds.Tables[0].Rows[0]["OutdatedFee"].ToString() != "")
                {
                    model.OutdatedFee = decimal.Parse(ds.Tables[0].Rows[0]["OutdatedFee"].ToString());
                }
                model.DeadLineType = ds.Tables[0].Rows[0]["DeadLineType"].ToString();

                if (ds.Tables[0].Rows[0]["CurrentMax"].ToString() != "")
                {
                    model.CurrentMax = Convert.ToInt64(ds.Tables[0].Rows[0]["CurrentMax"].ToString());
                }
                else
                    model.CurrentMax = 0;

                if (ds.Tables[0].Rows[0]["IsGetCar"].ToString() != "")
                {
                    model.IsGetCar = Convert.ToBoolean(ds.Tables[0].Rows[0]["IsGetCar"].ToString());
                }
                else
                    model.IsGetCar = false;

                if (ds.Tables[0].Rows[0]["EarlyRefund"].ToString() != "")
                {
                    model.EarlyRefund = decimal.Parse(ds.Tables[0].Rows[0]["EarlyRefund"].ToString());
                }

                return model;
            }
            else
            {
                return null;
            }
        }

        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id,Code,CustId,CarId,Rent,Deposit,ZPrice,DeadLine,UserId,BookTime,Marks,InpuTime,BPrice,IsStar,ysTime,LivAddr,AriAddr,FlayCode,SenAddr,PayType,Type,EndBooktime,Endystime,PriceType1,PriceType2,OutdatedFee,DeadLineType,CurrentMax");
            strSql.Append(" FROM Reserve ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SqlHelper.Query(strSql.ToString());
        }

        public DataSet GetList2(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select reserve.Id,reserve.Code,CustId,CarId,Rent,Deposit,ZPrice,DeadLine,reserve.UserId,BookTime,reserve.Marks,InpuTime,BPrice,IsStar,ysTime,LivAddr,AriAddr,FlayCode,SenAddr,PayType,Type,EndBooktime,Endystime,PriceType1,PriceType2,OutdatedFee,DeadLineType,car.code as carcode");
            strSql.Append(" FROM Reserve left join car on car.id=reserve.carid ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SqlHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 还车
        /// </summary>
        /// <param name="carID"></param>
        /// <param name="reserveId"></param>
        /// <returns></returns>
        public bool FinishOrder(int carID, int reserveId, Int64 max, Model.Reserve model, decimal InputBillingAmount)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@CarId", SqlDbType.Int,4),
                    new SqlParameter("@ReserveId",  SqlDbType.Int,4),
                    new SqlParameter("@Max",  SqlDbType.BigInt),
                    new SqlParameter("@Marks",  SqlDbType.NVarChar),
                    new SqlParameter("@MealsAccommodation",  SqlDbType.Decimal),
                    new SqlParameter("@IllegalRetention",  SqlDbType.Decimal),
                    new SqlParameter("@RentReceivable",  SqlDbType.Decimal),
                    new SqlParameter("@ReceivedAmount",  SqlDbType.Decimal),
                    new SqlParameter("@OvertimeAmount",  SqlDbType.Decimal),
                    new SqlParameter("@SuperDriverAmount",  SqlDbType.Decimal),
                    new SqlParameter("@TotalFines",  SqlDbType.Decimal),
                    new SqlParameter("@DepositReturn",  SqlDbType.Decimal),
                    new SqlParameter("@RepairDamage",  SqlDbType.Decimal),
                    new SqlParameter("@TotalAmount",  SqlDbType.Decimal),
                    new SqlParameter("@IsCompletedCheckout",  SqlDbType.Bit),
                    new SqlParameter("@IsReturnIllegal",  SqlDbType.Bit),
                    new SqlParameter("@SettlementDate",  SqlDbType.DateTime),
                    new SqlParameter("@SettlementUserID",  SqlDbType.Int,4),
					new SqlParameter("@CustId", SqlDbType.Int,4),
                    new SqlParameter("@InputBillingAmount",  SqlDbType.Decimal),
                    new SqlParameter("@EarlyRefund",  SqlDbType.Decimal),
					new SqlParameter("@Points", SqlDbType.BigInt),
					new SqlParameter("@PointRate", SqlDbType.Float)
                                        };
            parameters[0].Value = carID;
            parameters[1].Value = reserveId;
            parameters[2].Value = max;
            parameters[3].Value = model.Marks;
            parameters[4].Value = model.MealsAccommodation;
            parameters[5].Value = model.IllegalRetention;

            parameters[6].Value = model.RentReceivable;
            parameters[7].Value = model.ReceivedAmount;
            parameters[8].Value = model.OvertimeAmount;
            parameters[9].Value = model.SuperDriverAmount;
            parameters[10].Value = model.TotalFines;
            parameters[11].Value = model.DepositReturn;
            parameters[12].Value = model.RepairDamage;
            parameters[13].Value = model.TotalAmount;
            parameters[14].Value = model.IsCompletedCheckout;
            parameters[15].Value = model.IsReturnIllegal;

            parameters[16].Value = model.SettlementDate;
            parameters[17].Value = model.SettlementUserID;
            parameters[18].Value = model.CustId;
            parameters[19].Value = InputBillingAmount;
            parameters[20].Value = model.EarlyRefund;
            parameters[21].Value = model.Points;
            parameters[22].Value = model.PointRate;


            int intResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "Reserve_FinishOrder", parameters);
            if (intResult > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 违章保留退款
        /// </summary>
        /// <param name="carID"></param>
        /// <param name="reserveId"></param>
        /// <returns></returns>
        public bool ReturnIllegal(Model.Reserve model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@ReserveId", SqlDbType.Int,4),
                    new SqlParameter("@ReturnIllegalUserID",  SqlDbType.Int,4),
                    new SqlParameter("@ReturnIllegalAmount",  SqlDbType.Decimal),
                    new SqlParameter("@ReturnIllegalDate",   SqlDbType.DateTime),
					new SqlParameter("@CustId", SqlDbType.Int,4)
                                        };
            parameters[0].Value = model.Id;
            parameters[1].Value = model.ReturnIllegalUserID;
            parameters[2].Value = model.ReturnIllegalAmount;
            parameters[3].Value = model.ReturnIllegalDate;
            parameters[4].Value = model.CustId;

            int intResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "Reserve_ReturnIllegal", parameters);
            if (intResult > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 获取空闲订车时间列表
        /// </summary>
        /// <param name="carID"></param>
        /// <returns></returns>
        public DataSet GetFreeDateByCarID(int carID)
        {
            DataSet dsResult = new DataSet();
            SqlParameter[] parameters = {
					new SqlParameter("@CarId", SqlDbType.Int,4)
            };

            parameters[0].Value = carID;

            DataSet ds = SqlHelper.GetDataFormDataSet(CommandType.StoredProcedure, "Reserve_GetFreeDateByCarID", parameters);

            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                // Create a new DataTable.
                System.Data.DataTable table = new DataTable("ParentTable");
                // Declare variables for DataColumn and DataRow objects.
                DataColumn column;
                DataRow row;
                // Create new DataColumn, set DataType, 
                // ColumnName and add to DataTable.    
                column = new DataColumn();
                column.DataType = System.Type.GetType("System.Int32");
                column.ColumnName = "id";
                column.ReadOnly = true;
                column.Unique = true;
                // Add the column to the table.
                table.Columns.Add(column);

                // Create second column.
                column = new DataColumn();
                column.DataType = System.Type.GetType("System.String");
                column.ColumnName = "BeginDate";
                column.AutoIncrement = false;
                column.ReadOnly = false;
                column.Unique = false;
                // Add the column to the table.
                table.Columns.Add(column);

                // Create second column.
                column = new DataColumn();
                column.DataType = System.Type.GetType("System.String");
                column.ColumnName = "EndDate";
                column.AutoIncrement = false;
                column.ReadOnly = false;
                column.Unique = false;
                // Add the column to the table.
                table.Columns.Add(column);

                // Make the ID column the primary key column.
                DataColumn[] PrimaryKeyColumns = new DataColumn[1];
                PrimaryKeyColumns[0] = table.Columns["id"];
                table.PrimaryKey = PrimaryKeyColumns;
                // Instantiate the DataSet variable.
                dsResult = new DataSet();
                // Add the new DataTable to the DataSet.


                // Create three new DataRow objects and add                 
                // them to the DataTable

                if (ds.Tables[0].Rows.Count > 0)
                {
                    DateTime dtFirst = Convert.ToDateTime(ds.Tables[0].Rows[0]["BookTime"].ToString());
                    DateTime dtNow = DateTime.Now;

                    //if (dtFirst.Subtract(dtNow).TotalSeconds > 0)
                    //{
                    row = table.NewRow();
                    row["id"] = -1;
                    row["BeginDate"] = "";
                    row["EndDate"] = dtFirst.ToString();
                    table.Rows.Add(row);
                    //}
                }

                for (int i = 0; ds.Tables[0].Rows.Count > i; i++)
                {
                    row = table.NewRow();
                    row["id"] = i;
                    row["BeginDate"] = ds.Tables[0].Rows[i]["EndBooktime"].ToString();
                    if (i == ds.Tables[0].Rows.Count - 1)
                    {
                        row["EndDate"] = "";
                    }
                    else
                    {
                        row["EndDate"] = ds.Tables[0].Rows[i + 1]["BookTime"].ToString();
                    }
                    table.Rows.Add(row);
                }
                dsResult.Tables.Add(table);
            }

            return dsResult;
        }

    }
}
